#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd
from xpy3lib.utils import db_utils as util
from xpy3lib.XLogger import XLogger
from sicost.config import app_config
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.dp_common_job import DP011MJob__df7


class DP011_Z_B_Job(AbstractDPJob):
    """
    """

    cost_center_org = None
    data_type = None

    # NOTE p_wce_org =v_wce,
    # NOTE p_wce=LEFT(v_wce,1)||'@@@@'
    wce_org = None
    wce = None

    cal_type = None
    coef = None
    mon = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center_org=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None,
                 p_wce_org=None,
                 p_wce=None,
                 p_cal_type=None,
                 p_coef=None,
                 p_mon=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center_org:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        :param p_wce_org: 成本科目编号
        :param p_wce:
        :param p_cal_type:
        :param p_coef:
        :param p_mon:
        """
        super(DP011_Z_B_Job, self).__init__(p_config=p_config,
                                          p_db_conn_mpp=p_db_conn_mpp,
                                          p_db_conn_rds=p_db_conn_rds,
                                          p_db_conn_dbprod7=p_db_conn_dbprod7,
                                          p_unit=p_unit,
                                          p_account=p_account,
                                          p_cost_center=p_cost_center,
                                          p_account_period_start=p_account_period_start,
                                          p_account_period_end=p_account_period_end)
        self.cost_center_org = p_cost_center_org
        self.data_type = p_data_type
        self.wce_org = p_wce_org
        self.wce = p_wce
        self.cal_type = p_cal_type
        self.coef = p_coef
        self.mon = p_mon

        profile = 'dev'
        config = app_config[profile]
        self.db_conn_mpp2 = util.getConnectionDb2(config.DB_HOST_MPP2_DB2,
                                                  config.DB_PORT_MPP2_DB2,
                                                  config.DB_DBNAME_MPP2_DB2,
                                                  config.DB_USER_MPP2_DB2,
                                                  config.DB_PASSWORD_MPP2_DB2)
    def do_execute(self):
        """
        """
        self.logger.info('DP011_Z_B_Job.do_execute')
        df0, success = self.__step_0()
        if success is False:
            return
        df0.columns = df0.columns.str.upper()

        SOURCE_TABLE_NUM = df0.loc[0]['SOURCE_TABLE_NUM']
        CAL_COEF1 = df0.loc[0]['CAL_COEF1']
        CAL_COEF2 = df0.loc[0]['CAL_COEF2']
        CAL_WHERE1 = df0.loc[0]['CAL_WHERE1']
        CAL_OTHERS = df0.loc[0]['CAL_OTHERS']
        CAL_WHERE2 = df0.loc[0]['CAL_WHERE2']
        CAL_WHERE3 = df0.loc[0]['CAL_WHERE3']
        CAL_COLUMN1 = df0.loc[0]['CAL_COLUMN1']
        CAL_COLUMN2 = df0.loc[0]['CAL_COLUMN2']
        CAL_COLUMN3 = df0.loc[0]['CAL_COLUMN3']
        PARM_1 = df0.loc[0]['PARM_1']

        if SOURCE_TABLE_NUM == '3':
            df1, success = self.__step_2(CAL_COLUMN1, CAL_WHERE1)
            if success is False:
                return
            df1.columns = df1.columns.str.upper()
            df2, success = self.__step_3(CAL_COLUMN2, CAL_WHERE2)
            if success is False:
                return
            df2.columns = df2.columns.str.upper()
            v = eval(CAL_COEF1)
            df12 = pd.merge(df1, df2, on=v, how='left')
            df3, success = self.__step_4(CAL_COLUMN3, CAL_WHERE3)
            if success is False:
                return
            df3.columns = df3.columns.str.upper()
            v = eval(CAL_COEF2)
            df_x = pd.merge(df12, df3, on=v, how='left')
            self.logger.info(df_x)
            #CAL_OTHERS = "df_x['ACT_N'] = df_x['B_ELE_TRICITY'] * df_x['A_WT'] * 1.000000 / df_x['C_WT'] * df_x['A_WT'] / 10000',df_x['CONSUME_N'] = df_x['B_ELE_TRICITY'] * df_x['A_WT'] * 1.000000 / df_x['C_WT'] * df_x['A_WT'] / 10000"
            #CAL_COEF1 = ['A_WT', 'B_ELE_TRICITY', 'C_WT']
            #解析后将df生成ACT_N,CONSUME_N两列，删除A_WT','B_ELE_TRICITY','C_WT'三列
            CAL_OTHERS = CAL_OTHERS.split(',')
            #df_x['ACT_N'] =  df_x['B_ACT_N'] * df_x['A_ACT_WT'] / df_x['C_SUM_WT']
            for i in CAL_OTHERS:
                exec(i)
            #...
            #PARM_1 = PARM_1.split(',')
            PARM_1 = eval(PARM_1)
            # xlsx_name = '测试'
            # xlsx_name = xlsx_name + '.xlsx'
            # print(xlsx_name)
            # writer = pd.ExcelWriter(xlsx_name)
            # df_x.to_excel(writer, sheet_name='sheet1')
            # writer.save()
            #PARM_1 = ['A_ACT_WT','T_DATE','T_DATE2','B_ACT_N','C_SUM_WT']
            for i in PARM_1:
                df_x.drop([i], axis=1, inplace=True)

            df_x.drop(['REC_ID'], axis=1, inplace=True)
            df_x['REC_CREATOR'] = df_x['REC_REVISOR']
            df_x['REC_CREATE_TIME'] = df_x['REC_REVISOR_TIME']

            df_x.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
            df_x.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
            df_x.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
            df_x.rename(columns={'TEAM': 'CLASS'}, inplace=True)
            df_x.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
            df_x.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
            df_x.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
            df_x.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
            df_x.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
            df_x.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
            df_x.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
            df_x.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
            df_x.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
            df_x.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
            df_x.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
            df_x.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
            df_x.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
            df_x.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
            # df31.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
            df_x.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
            df_x.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
            df_x.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
            df_x.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
            df_x.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
            df_x.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
            df_x.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
            df_x.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
            df_x.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
            df_x.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
            df_x.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
            df_x.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
            df_x.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
            df_x.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
            df_x.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
            XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                           p_dataframe=df_x,
                           p_max_times=5).redo()

        if SOURCE_TABLE_NUM == '2':
            df1, success = self.__step_2(self, CAL_COLUMN1, CAL_WHERE1)
            if success is False:
                return
            df2, success = self.__step_3(self, CAL_COLUMN2, CAL_WHERE2)
            if success is False:
                return
            v = eval(CAL_COEF1)
            df_x = pd.merge(df1, df2, on=v, how='left')
            self.logger.info(df_x)
            #CAL_OTHERS = "df_x['ACT_N'] = df_x['B_ELE_TRICITY'] * df_x['A_WT'] * 1.000000 / df_x['C_WT'] * df_x['A_WT'] / 10000',df_x['CONSUME_N'] = df_x['B_ELE_TRICITY'] * df_x['A_WT'] * 1.000000 / df_x['C_WT'] * df_x['A_WT'] / 10000"
            #CAL_COEF1 = ['A_WT', 'B_ELE_TRICITY', 'C_WT']
            #解析后将df生成ACT_N,CONSUME_N两列，删除A_WT','B_ELE_TRICITY','C_WT'三列
            CAL_OTHERS = CAL_OTHERS.split(',')
            for i in CAL_OTHERS:
                exec(i)
            PARM_1 = eval(PARM_1)
            for i in PARM_1:
                df_x.drop([i], axis=1, inplace=True)

            df_x.drop(['REC_ID'], axis=1, inplace=True)
            df_x['REC_CREATOR'] = df_x['REC_REVISOR']
            df_x['REC_CREATE_TIME'] = df_x['REC_REVISOR_TIME']

            df_x.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
            df_x.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
            df_x.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
            df_x.rename(columns={'TEAM': 'CLASS'}, inplace=True)
            df_x.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
            df_x.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
            df_x.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
            df_x.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
            df_x.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
            df_x.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
            df_x.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
            df_x.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
            df_x.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
            df_x.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
            df_x.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
            df_x.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
            df_x.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
            df_x.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
            # df31.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
            df_x.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
            df_x.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
            df_x.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
            df_x.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
            df_x.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
            df_x.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
            df_x.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
            df_x.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
            df_x.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
            df_x.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
            df_x.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
            df_x.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
            df_x.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
            df_x.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
            df_x.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)

            XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                           p_dataframe=df_x,
                           p_max_times=5).redo()
        if SOURCE_TABLE_NUM == '1':

            cost_center = str(self.cost_center)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_COST_CENTER', cost_center)
            unit = str(self.unit)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_UNIT', unit)
            data_type = str(self.data_type)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_DATA_TYPE', data_type)
            wce = str(self.wce)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_WCE', wce)
            account_period_start = str(self.account_period_start)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_START_TIME', account_period_start)
            account_period_end = str(self.account_period_end)


            CAL_COLUMN1 = CAL_COLUMN1.replace('P_END_TIME', account_period_end)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_ACCOUNT_PERIOD_START', account_period_start)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_ACCOUNT_PERIOD_END', account_period_end)
            #CAL_COLUMN1 = CAL_COLUMN1.replace('SU_AJBG_DP0102', 'SU_AJBG_DP0102_TMP')

            account = str(self.account)
            CAL_COLUMN1 = CAL_COLUMN1.replace('P_ACCOUNT', account)


            sql = " %s" % (CAL_COLUMN1)

            self.logger.info(sql)
            if CAL_WHERE1 == 'rds':
                df_x = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
            if CAL_WHERE1 == 'db2':
                df_x = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            if CAL_WHERE1 == 'mpp':
                df_x = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
            #df_x = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()

            self.logger.info(df_x)
            success = df_x.empty is False
            if success is False:
                return
            df_x.columns = df_x.columns.str.upper()
            df_x.drop(['REC_ID'], axis=1, inplace=True)
            df_x['REC_CREATOR'] = df_x['REC_REVISOR']
            df_x['REC_CREATE_TIME'] = df_x['REC_REVISOR_TIME']

            df_x.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
            df_x.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
            df_x.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
            df_x.rename(columns={'TEAM': 'CLASS'}, inplace=True)
            df_x.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
            df_x.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
            df_x.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
            df_x.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
            df_x.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
            df_x.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
            df_x.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
            df_x.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
            df_x.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
            df_x.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
            df_x.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
            df_x.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
            df_x.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
            df_x.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
            # df_x.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
            df_x.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
            df_x.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
            df_x.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
            df_x.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
            df_x.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
            df_x.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
            df_x.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
            df_x.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
            df_x.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
            df_x.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
            df_x.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
            df_x.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
            df_x.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
            df_x.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
            df_x.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)

            XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                           p_dataframe=df_x,
                           p_max_times=5).redo()





        super(DP011_Z_B_Job, self).do_execute()

    def __step_0(self, p_cal_type=None, p_coef=None, p_mon=None):
        # 子步骤0
        # 读取维护配置表
        # 读出关联的表名，关联字段，补充的SQL语句A表，补充的SQL语句B表，补充的字段A与B 等等 待补充 还没建 FIXME TBD????
        #
        # 1001 MIDF Q161 0 59068
        # 是传入的参数
        # p_account p_cost_center p_unit p_data_type P_WCE_ORG
        # 得到
        # 关联表的表名
        # merge需要on的字段
        # 拼接的SQL
        sql = " SELECT " \
              " CALC_SQL as SOURCE_TABLE_NUM, " \
              " DATA_SOURCE_1 as CAL_WHERE1," \
              " DATA_SOURCE_2 as CAL_WHERE2," \
              " DATA_SOURCE_3 as CAL_WHERE3," \
              " COL_PROC_1 as CAL_COLUMN1," \
              " COL_PROC_2 as CAL_COLUMN2," \
              " COL_PROC_3 as CAL_COLUMN3," \
              " RELATED_TERM_1 as CAL_COEF1," \
              " RELATED_TERM_2 as CAL_COEF2," \
              " OTHER_PROC as CAL_OTHERS," \
              " PARM_1 " \
              " FROM BGTARAS1.T_ADS_WH_SICB_DP011B" \
              " WHERE ACCT='%s'" \
              " AND COST_CENTER='%s'" \
              " AND UNIT_CODE='%s'" \
              " AND SOURCE_CODE='%s'" \
              " AND COST_SUBJECT ='%s'" % (self.account,
                                  self.cost_center,
                                  self.unit,
                                  self.data_type,
                                  self.wce_org)
        self.logger.info(sql)
        df0 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        self.logger.info(df0)
        success = df0.empty is False


        return df0, success

    def __step_1(self, p_cal_type=None, p_coef=None, p_mon=None):
        pass


    def __step_2(self, CAL_COLUMN1, CAL_WHERE1):

        cost_center = str(self.cost_center)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_COST_CENTER', cost_center)
        unit = str(self.unit)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_UNIT', unit)
        data_type = str(self.data_type)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_DATA_TYPE', data_type)
        wce = str(self.wce_org)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_WCE', wce)
        account_period_start = str(self.account_period_start)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_ACCOUNT_PERIOD_START', account_period_start)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_START_TIME', account_period_start)
        account_period_end = str(self.account_period_end)

        CAL_COLUMN1 = CAL_COLUMN1.replace('P_ACCOUNT_PERIOD_END', account_period_end)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_END_TIME', account_period_start)
        account = str(self.account)
        CAL_COLUMN1 = CAL_COLUMN1.replace('P_ACCOUNT', account)
        CAL_COLUMN1 = CAL_COLUMN1.replace('SU_AJBG_DP0102', 'SU_AJBG_DP0102_TMP')

        sql = " %s" % (CAL_COLUMN1)

        self.logger.info(sql)
        if CAL_WHERE1 == 'rds':
            df1 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        if CAL_WHERE1 == 'db2':
            df1 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        if CAL_WHERE1 == 'mpp':
            df1 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        #df1 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()

        success = df1.empty is False
        #if success is False:
        #    return
        #df1.columns = df1.columns.str.upper()
        #self.logger.info(df1)
        return df1, success


    def __step_3(self, CAL_COLUMN2, CAL_WHERE2):



        cost_center = str(self.cost_center)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_COST_CENTER', cost_center)
        unit = str(self.unit)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_UNIT', unit)
        data_type = str(self.data_type)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_DATA_TYPE', data_type)
        wce = str(self.wce_org)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_WCE', wce)
        account_period_start = str(self.account_period_start)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_ACCOUNT_PERIOD_START', account_period_start)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_START_TIME', account_period_start)
        account_period_end = str(self.account_period_end)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_ACCOUNT_PERIOD_END', account_period_end)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_END_TIME', account_period_start)
        account = str(self.account)
        CAL_COLUMN2 = CAL_COLUMN2.replace('P_ACCOUNT', account)

        sql = " %s" % (CAL_COLUMN2)

        self.logger.info(sql)
        if CAL_WHERE2 == 'rds':
            df2 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        if CAL_WHERE2 == 'db2':
            df2 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        if CAL_WHERE2 == 'mpp':
            df2 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        #df2 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()

        self.logger.info(df2)
        success = df2.empty is False
        #if success is False:
        #    return
        #df2.columns = df2.columns.str.upper()
        return df2, success


    def __step_4(self, CAL_COLUMN3, CAL_WHERE3):

        cost_center = str(self.cost_center)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_COST_CENTER', cost_center)
        unit = str(self.unit)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_UNIT', unit)
        data_type = str(self.data_type)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_DATA_TYPE', data_type)
        wce = str(self.wce_org)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_WCE', wce)
        account_period_start = str(self.account_period_start)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_ACCOUNT_PERIOD_START', account_period_start)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_START_TIME', account_period_start)
        account_period_end = str(self.account_period_end)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_ACCOUNT_PERIOD_END', account_period_end)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_END_TIME', account_period_start)
        account = str(self.account)
        CAL_COLUMN3 = CAL_COLUMN3.replace('P_ACCOUNT', account)

        sql = " %s" % (CAL_COLUMN3)

        self.logger.info(sql)
        if CAL_WHERE3 == 'rds':
            df3 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        if CAL_WHERE3 == 'db2':
            df3 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        if CAL_WHERE3 == 'mpp':
            df3 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        success = df3.empty is False
        #if success is False:
        #    return
        #df3.columns = df3.columns.str.upper()
        #self.logger.info(df3)

        return df3, success
